﻿using System;
using System.Data;
using System.Data.Common;
using System.Text;
using System.Text.RegularExpressions;
using System.Collections;
using System.Collections.Generic;

using System.Data.SqlClient;

namespace VX.DBTransfer.Framework
{
    public class DataParameter
    {
        public string ParameterName = "";
        public DataType Type = DataType.NVarChar;
        public int Size;
        public object Value = null;
        public ParameterDirection Direction = ParameterDirection.Input;
        public DataParameter(string name, DataType type)
        {
            ParameterName = name;
            Type = type;
        }
        public DataParameter(string name, int size)
        {
            ParameterName = name;
            Type = DataType.NVarChar;
            Size = size;
        }
        public DataParameter(string name, DataType type, int size)
        {
            ParameterName = name;
            Type = type;
            Size = size;
        }
    }

    public class SQLDBAccess
    {
        private string m_strMessage;
        private string m_strSQL;
        public bool IsSuccess;
        public bool ParseOracle = true;
        private DbConnection m_oConn;
        private DbCommand m_dbCmd;
        private DbDataAdapter m_oAdpt;
        private DbTransaction m_oTran;
        private enmDBDriver m_DBDriver;
        public bool NotCatchException = true;

        public SQLDBAccess(enmDBDriver DBDriver)
        {
            m_oConn = null;
            m_dbCmd = null;
            m_oAdpt = null;
            m_oTran = null;
            IsSuccess = false;
            m_DBDriver = DBDriver;
        }
        public System.Data.IDbConnection iConnection
        {
            get
            {
				
                return m_oConn;
            }
        }
        public string Message
        {
            get
            {
                return m_strMessage;
            }
            set
            {
                m_strMessage = value;
            }
        }
        public string SQL
        {
            get
            {
                return m_strSQL;
            }
            set
            {
                m_strSQL = value;
            }
        }
        public DbCommand DatabaseCommand
        {
            get
            {
                return m_dbCmd;
            }
        }
        public int ExecSP(ref IList<DataParameter> Parameters)
        {
            int iReturn = -1;
            m_dbCmd.CommandType = CommandType.StoredProcedure;

            PrepareExecute(Parameters);

            try
            {
                iReturn = m_dbCmd.ExecuteNonQuery();
                IsSuccess = true;
                return iReturn;
            }
            catch (System.Exception objErr)
            {
                IsSuccess = false;
                m_strMessage = objErr.Message;
                return -1;
            }
            finally
            {
                CloseExecute(ref Parameters);
            }
        }

        public DataTable GetDataTableSP(ref IList<DataParameter> Parameters)
        {
            m_dbCmd.CommandType = CommandType.StoredProcedure;

            PrepareExecute(Parameters);

            try
            {
                DataTable dtReturn = new DataTable();
                m_oAdpt.Fill(dtReturn);
                IsSuccess = true;
                return dtReturn;
            }
            catch (System.Exception objErr)
            {
                IsSuccess = false;
                m_strMessage = objErr.Message;
                return null;
            }
            finally
            {
                CloseExecute(ref Parameters);
            }
        }

        public DataSet GetDataSetSP(ref IList<DataParameter> Parameters)
        {
            m_dbCmd.CommandType = CommandType.StoredProcedure;

            PrepareExecute(Parameters);

            try
            {
                DataSet dsReturn = new DataSet();
                m_oAdpt.Fill(dsReturn);
                IsSuccess = true;
                return dsReturn;
            }
            catch (System.Exception objErr)
            {
                IsSuccess = false;
                m_strMessage = objErr.Message;
                return null;
            }
            finally
            {
                CloseExecute(ref Parameters);
            }
        }

        public int ExecSQL(ref IList<DataParameter> Parameters)
        {
            int iReturn = -1;
            m_dbCmd.CommandType = CommandType.Text;

            PrepareExecute(Parameters);
            try
            {
                iReturn = m_dbCmd.ExecuteNonQuery();
                IsSuccess = true;
                return iReturn;
            }
            catch (System.Exception objErr)
            {
                IsSuccess = false;
                m_strMessage = objErr.Message;
                throw;
              
            }
            finally
            {
                CloseExecute(ref Parameters);
            }
        }

        public object ExecuteScalar(ref IList<DataParameter> Parameters)
        {
            object returnScalar;
            m_dbCmd.CommandType = CommandType.Text;

            PrepareExecute(Parameters);
            try
            {
                returnScalar = m_dbCmd.ExecuteScalar();
                IsSuccess = true;
                return returnScalar;
            }
            catch (Exception objErr)
            {
                IsSuccess = false;
                m_strMessage = objErr.Message;
                throw;
            }
            finally
            {
                CloseExecute(ref Parameters);
            }
        }

        public DataSet GetDataSet(ref IList<DataParameter> Parameters)
        {
            m_dbCmd.CommandType = CommandType.Text;

            PrepareExecute(Parameters);

            try
            {
                DataSet dtReturn = new DataSet();
                m_oAdpt.Fill(dtReturn);
                IsSuccess = true;
                return dtReturn;
            }
            catch (System.Exception objErr)
            {
                IsSuccess = false;
                m_strMessage = objErr.Message;
                return null;
            }
            finally
            {
                CloseExecute(ref Parameters);
            }
        }

        public DataTable GetDataTable(ref IList<DataParameter> Parameters)
        {
            m_dbCmd.CommandType = CommandType.Text;

            PrepareExecute(Parameters);

            try
            {
                DataTable dtReturn = new DataTable();
                m_oAdpt.Fill(dtReturn);
                IsSuccess = true;
                return dtReturn;
            }
            catch (System.Exception objErr)
            {
                IsSuccess = false;
                m_strMessage = objErr.Message;
                throw;
            }
            finally
            {
                CloseExecute(ref Parameters);
            }
        }

        public DbDataReader GetReader(ref IList<DataParameter> Parameters)
        {
            DbDataReader drReturn;
            m_dbCmd.CommandType = CommandType.Text;

            PrepareExecute(Parameters);

            try
            {
                drReturn = m_dbCmd.ExecuteReader();
                IsSuccess = true;
                return drReturn;
            }
            catch (System.Exception objErr)
            {
                IsSuccess = false;
                m_strMessage = objErr.Message;
                return null;
            }
            finally
            {
                CloseExecute(ref Parameters);
            }
        }

        public bool IsOpen()
        {
            if (m_oConn != null && m_oConn.State != System.Data.ConnectionState.Closed)
                return true;
            else
                return false;
        }

        public bool Open(string strConn)
        {
            Dispose();
            try
            {
                m_oConn = new SqlConnection(strConn);
                m_dbCmd = new SqlCommand();
                m_oAdpt = new SqlDataAdapter();

                m_dbCmd.Connection = m_oConn;
                m_oAdpt.SelectCommand = m_dbCmd;

                m_oConn.ConnectionString = strConn;
                m_oConn.Open();
                return true;
            }
            catch (Exception ex)
            {
                m_strMessage = ex.Message;
                return false;
            }
        }

        public void NewDatabaseCommand()
        {
            m_dbCmd = new SqlCommand();
            m_dbCmd.Connection = m_oConn;
            m_oAdpt.SelectCommand = m_dbCmd;
        }

        public void Dispose()
        {
            if (m_oTran != null)
            {
                m_oTran.Rollback();
                m_oTran.Dispose();
                m_oTran = null;
            }

            if (m_oAdpt != null)
            {
                m_oAdpt.Dispose();
                m_oAdpt = null;
            }

            if (m_dbCmd != null)
            {
                m_dbCmd.Dispose();
                m_dbCmd = null;
            }

            if (m_oConn != null)
            {
                if (m_oConn.State != System.Data.ConnectionState.Closed)
                    m_oConn.Close();

                m_oConn.Dispose();
                m_oConn = null;
            }
            //System.GC.Collect();
        }

        public bool BeginTran()
        {
            try
            {
                m_oTran = m_oConn.BeginTransaction();
                m_dbCmd.Transaction = m_oTran;
                IsSuccess = true;
                return true;
            }
            catch (Exception ex)
            {
                IsSuccess = false;
                m_strMessage = ex.Message;
                return false;
            }
        }

        public void Commit()
        {
            try
            {
                m_oTran.Commit();
                m_oTran.Dispose();
                m_oTran = null;
            }
            catch
            {
                ;
            }
        }

        public void Rollback()
        {
            try
            {
                m_oTran.Rollback();
                m_oTran.Dispose();
                m_oTran = null;
            }
            catch
            {
                ;
            }
        }

        //ORACLE出错信息处理
        //  在SQL脚本中，可以通过RAISE_APPLICATION_ERROR来返回特定的信息编码。
        //  在业务逻辑层可以根据该信息编码，确定当前的详细错误信息。
        private string ProcessOracleMessage(string strMsg)
        {
            string s = strMsg;
            //-20001是由解析器加上的固定错误编码，本行后续内容即RAISERROR函数的第一个参数。
            if (s.ToUpper().ToUpper().StartsWith("ORA-20001:"))
            {
                int i = s.IndexOf("\n");
                if (i > 0)
                {
                    s = s.Substring(11, i - 11).Trim();
                }
            }
            return s;
        }

        private void CloseExecute(ref IList<DataParameter> Parameters)
        {
            DataParameter dbPara;

            for (int i = 0; i < m_dbCmd.Parameters.Count; i++)
            {
                if (m_dbCmd.Parameters[i].Direction != ParameterDirection.Input)
                {
                    dbPara = (DataParameter)Parameters[i];
                    dbPara.Value = m_dbCmd.Parameters[i].Value;
                    Parameters[i] = dbPara;
                }
            }
            m_dbCmd.Parameters.Clear();
            m_dbCmd.CommandTimeout = 30;
        }

        private void PrepareExecute(IList<DataParameter> Parameters)
        {
            DataParameter dbPara;
            SqlParameter sqlPara = null;
            m_strSQL = m_strSQL.TrimStart(';');
            string strSQL = m_strSQL;




            m_dbCmd.CommandTimeout = 0;
            m_dbCmd.Parameters.Clear();
            if (Parameters != null && Parameters.Count != 0)
            {
                for (int i = 0; i < Parameters.Count; i++)
                {
                    dbPara = (DataParameter)Parameters[i];
                    sqlPara = new SqlParameter();
                    sqlPara.ParameterName = dbPara.ParameterName;
                    //对于NVarChar的输出型参数，必须设置长度。
                    if (dbPara.Size != 0) sqlPara.Size = dbPara.Size;
                    sqlPara.Direction = dbPara.Direction;
                    sqlPara.Value = dbPara.Value;
                    switch (dbPara.Type)
                    {
                        case DataType.OracleCursor:
                            break;
                        case DataType.SQLStatement:
                            sqlPara.SqlDbType = SqlDbType.NVarChar;
                            sqlPara.Size = 4000;
                            break;
                        case DataType.Bit:
                            sqlPara.SqlDbType = SqlDbType.Bit;

                            break;
                        case DataType.DateTime:
                            sqlPara.IsNullable = true;
                            sqlPara.SqlDbType = SqlDbType.DateTime;
                            if (dbPara.Value == null)
                            {
                                strSQL = strSQL.Replace(dbPara.ParameterName, "null");
                                continue;
                            }
                            break;
                        case DataType.Float:
                            sqlPara.SqlDbType = SqlDbType.Float;
                            break;
                        case DataType.BigInt:
                            sqlPara.SqlDbType = SqlDbType.BigInt;
                            break;
                        case DataType.Int:
                            sqlPara.SqlDbType = SqlDbType.Int;
                            break;
                        case DataType.Money:
                            sqlPara.SqlDbType = SqlDbType.Money;
                            break;
                        case DataType.NText:
                            sqlPara.SqlDbType = SqlDbType.NText;
                            break;
                        case DataType.NVarChar:
                            sqlPara.SqlDbType = SqlDbType.NVarChar;
                            break;
                        case DataType.Real:
                            sqlPara.SqlDbType = SqlDbType.Real;
                            break;
                        case DataType.SmallInt:
                            sqlPara.SqlDbType = SqlDbType.SmallInt;
                            break;
                        case DataType.TinyInt:
                            sqlPara.SqlDbType = SqlDbType.TinyInt;
                            break;
                        case DataType.Binary:
                            sqlPara.SqlDbType = SqlDbType.Image;
                            break;
                    }
                    m_dbCmd.Parameters.Add(sqlPara);
                }
            }
            //针对不同的数据库登录用户调整脚本中的dbo.为对应用户
            //Modified by Chao Zeng 2015/06/25
            string connStr = m_dbCmd.Connection.ConnectionString.Replace(" ", "").ToLower();
            strSQL = ParseQueryString(connStr, strSQL);
            //End***************************************
            m_dbCmd.CommandText = strSQL;
        }
        /// <summary>
        /// 针对不同的数据库登录用户调整脚本中的dbo.为对应用户
        /// Author：Chao Zeng   Date：2015/06/25
        /// </summary>
        /// <param name="connStr">数据库连接字符串（用于获取数据库登录用户）</param>
        /// <param name="strSQL">待调整的数据库脚本</param>
        /// <returns>已调整好的数据库脚本</returns>
        private string ParseQueryString(string connStr, string strSQL)
        {
            string dbUser = string.Empty;
            if (connStr.IndexOf("userid") > -1)
            {
                string[] splitStrs = { "userid=" };
                string[] strArray = connStr.Split(splitStrs, StringSplitOptions.RemoveEmptyEntries);
                if (strArray.Length > 1)
                {
                    splitStrs = new string[] { ";" };
                    strArray = strArray[1].Split(splitStrs, StringSplitOptions.RemoveEmptyEntries);
                    if (strArray.Length > 0)
                    {
                        dbUser = strArray[0];
                    }
                }
            }
            if (dbUser != string.Empty && dbUser != "sa")
            {
                strSQL = strSQL.Replace("dbo.", dbUser + ".").Replace("[dbo].", "[" + dbUser + "].");
            }
            return strSQL;
        }
    }
}
